"""
Case Type   : Ubtree索引增强
Case Name   : 建表插入数据，创建ubtree索引指定pcr_导入同样表结构的数据到有索引的表
Create At   : 2025-11-17
Owner       : sungang14
Description :
    1、创建2个ustore表
    2、两表分别创建pcr索引
    3、表1插入数据
    4、执行COPY TO命令，导出表1数据，构造数据文件
    5、执行COPY FROM命令，向表2导入数据
    6、清理环境
Expect      :
    1、创建成功
    2、成功
    3、插入数据条成功
    4、导出数据文件成功
    5、导入数据成功
    6、清理环境成功
History     :
"""

import os
import time
import unittest
from yat.test import Node
from yat.test import macro
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

logger = Logger()


class SlowSQLTestCase(unittest.TestCase):
    def setUp(self):
        logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.comsh = CommonSH('PrimaryDbUser')
        self.userNode = Node('PrimaryDbUser')
        self.table_name01 = f"t_ustore_ubtree_009901"
        self.table_name02 = f"t_ustore_ubtree_009902"
        self.index_name01 = f"i_ustore_ubtree_009901"
        self.index_name02 = f"i_ustore_ubtree_009902"
        self.prepare_data_00 = f"drop table if exists {self.table_name01} cascade;drop table if exists {self.table_name02} cascade;"
        self.prepare_data_01 = f"create table {self.table_name01} (id int, name varchar(20)) with (storage_type=ustore);create table {self.table_name02} (id int, name varchar(20)) with (storage_type=ustore);"
        self.prepare_data_02 = f"create index {self.index_name01} on {self.table_name01} using ubtree(id) with (index_type=pcr);create index {self.index_name02} on {self.table_name02} using ubtree(id) with (index_type=pcr);"
        self.prepare_data_04 = f"insert into {self.table_name01} select g, 'name_'||g from generate_series(1,1000) as g;"
        self.copy_file = os.path.join(macro.DB_INSTANCE_PATH, 'ustore_ubtree_0099.dat')
        self.prepare_data_05 = f"copy {self.table_name01} to '{self.copy_file}'; "
        self.prepare_data_06 = f"copy {self.table_name02} from '{self.copy_file}'; "
        self.prepare_data_07 = f"select count(*) from {self.table_name02}; "

    def test_slowsql(self):
        text = '--step1:创建2个ustore表;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_00)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, data_cmd, '执行失败' + text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_01)
        logger.info(data_cmd)
        self.assertEqual(data_cmd.count(self.constant.TABLE_CREATE_SUCCESS), 2, '执行失败' + text)

        text = '--step2:两表分别创建pcr索引;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_02)
        logger.info(data_cmd)
        self.assertEqual(data_cmd.count(self.constant.CREATE_INDEX_SUCCESS_MSG), 2, '执行失败' + text)

        text = '--step3:表1插入测试数据;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_04)
        logger.info(data_cmd)
        self.assertIn(f"INSERT 0 1000", data_cmd, '执行失败' + text)

        text = '--step4:执行COPY TO命令，导出表1数据，构造数据文件;expect:成功--'
        logger.info(text)
        data_cmd_01 = self.comsh.execut_db_sql(self.prepare_data_05)
        logger.info(data_cmd_01)
        self.assertEqual(f"COPY 1000", data_cmd_01, '执行失败' + text)

        text = '--step5:执行COPY FROM命令，向表2导入数据;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_06)
        logger.info(data_cmd)
        self.assertEqual(f"COPY 1000", data_cmd_01, '执行失败' + text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_07)
        logger.info(data_cmd)
        self.assertEqual(f"1000", data_cmd.splitlines()[-2].strip(), '执行失败' + text)

    def tearDown(self):
        text = '--step8:清理环境，恢复默认值;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_00)
        logger.info(data_cmd)
        self.assertEqual(data_cmd.count(self.constant.TABLE_DROP_SUCCESS), 2, '执行失败' + text)
        logger.info(f'-----{os.path.basename(__file__)} end-----')
